package com.hfkh;

import java.sql.*;

public class Test {
    private static String DRIVER_PATH = "com.mysql.jdbc.Driver";
    private static String DATABASE_URL = "jdbc:mysql://localhost:3306/studentdatebase?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
    private static String USER_NAME = "root";
    private static String USER_PASSWORD = "541227";
    public static void insert(){
        Connection connection = null;
        PreparedStatement ps = null;
        int rows = 0;
        try {
            Class.forName(DRIVER_PATH);
            connection = DriverManager.getConnection(DATABASE_URL,USER_NAME,USER_PASSWORD);
            String sql = "insert into student (Sname,Ssex,Sage,Sdept)values(?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setInt(3,17);
            ps.setString(2,"男");
            ps.setString(1,"牛二");
            ps.setString(4,"机械工程系");
            rows = ps.executeUpdate();
            if (rows > 0){
                System.out.println("执行成功!");
            }else{
                System.out.println("执行失败!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (ps != null){
                try {
                    ps.close();
                    ps = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                    connection = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void update(){
        Connection connection = null;
        PreparedStatement ps = null;
        int rows = 0;
        try {
            Class.forName(DRIVER_PATH);
            connection = DriverManager.getConnection(DATABASE_URL,USER_NAME,USER_PASSWORD);
            String sql = "update student set Sage = ? where Sno = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,20);
            ps.setInt(2,1);
            rows = ps.executeUpdate();
            if (rows > 0){
                System.out.println("修改成功！" + rows);
            }else{
                System.out.println("修改失败！");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (ps != null){
                try {
                    ps.close();
                    ps = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                    connection = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void getAutoKey(){
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        int rows = 0,id = 0;
        try {
            connection = DriverManager.getConnection(DATABASE_URL,USER_NAME,USER_PASSWORD);
            String sql = "insert into student(Sname,Ssex,Sage,Sdept)values(?,?,?,?)";
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(3,18);
            ps.setString(1,"李四");
            ps.setString(2,"女");
            ps.setString(4,"园林艺术系");
            //rows = ps.executeUpdate();
            if (rows > 0){
                System.out.println("添加成功！");
                resultSet = ps.getGeneratedKeys();
                if (resultSet.next()){
                    id = resultSet.getInt(1);
                    System.out.println("添加的这个同学的学号是" + id);
                }
            }else{
                System.out.println("添加失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet != null){
                try {
                    resultSet.close();
                    resultSet = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null){
                try {
                    ps.close();
                    ps = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                    connection = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void delete(){
        Connection connection = null;
        PreparedStatement ps = null;
        int rows = 0;
        try {
            connection = DriverManager.getConnection(DATABASE_URL,USER_NAME,USER_PASSWORD);
            String sql = "delete from student where Sno = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,1);
            rows = ps.executeUpdate();
            if (rows > 0){
                System.out.println("删除成功！" + rows);
            }else {
                System.out.println("删除失败！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (ps != null){
                try {
                    ps.close();
                    ps = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                    connection = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void query(){
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = DriverManager.getConnection(DATABASE_URL,USER_NAME,USER_PASSWORD);
            String sql = "select Sno,Sname,Ssex,Sage,Sdept from student";
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            if (resultSet != null){
                while (resultSet.next()){
                    int id = resultSet.getInt("Sno");
                    String name = resultSet.getString("Sname");
                    String sex = resultSet.getString("Ssex");
                    int age = resultSet.getInt("Sage");
                    String dept = resultSet.getString("Sdept");
                    System.out.println("学号是" + id + "姓名是" + name + "性别是" + sex + "年龄是" + age + "系别是" + dept);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (resultSet != null){
                try {
                    resultSet.close();
                    resultSet = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null){
                try {
                    ps.close();
                    ps = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                    connection = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) {
        //insert();
        //update();
        getAutoKey();
        //delete();
        //query();
    }
}
